Provide workaround for core DISTINCT bug in db_rewrite_sql

Looks good to me!! I'll be happy to see this nasty bug get a work-around :)

Committed to 2.x. We're going to need a reroll against the 3.x branch.

here is a fast rerole

I have:
Latest Drupal 6
Views 6.x-2.x-dev 2009-Jun-26
Workflow 6.x-1.1

I had the SQL error when I would try and activate the Distinct ( SELECT DISTINCT(node.nid) AS DISTINCT(node.nid)....) , and then I updated to the latest dev View and now I get no error, but the duplicates are still there.

There is no "DISTINCT(node.nid)" in the SQL.

SELECT node.nid AS nid,
node_data_field_image.field_image_fid AS node_data_field_image_field_image_fid,
node_data_field_image.field_image_list AS node_data_field_image_field_image_list,
node_data_field_image.field_image_data AS node_data_field_image_field_image_data,
node.type AS node_type,
node.vid AS node_vid,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid,
node.title AS node_title,
vocabulary.name AS vocabulary_name,
node_revisions.body AS node_revisions_body,
node_revisions.format AS node_revisions_format,
node.sticky AS node_sticky,
node_data_field_date.field_date_value AS node_data_field_date_field_date_value,
DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 28800 SECOND), '%Y%m%d%H%i') AS node_created_minute
FROM node node
LEFT JOIN workflow_node workflow_node ON node.nid = workflow_node.nid
LEFT JOIN content_field_image node_data_field_image ON node.vid = node_data_field_image.vid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN vocabulary vocabulary ON term_data.vid = vocabulary.vid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid
WHERE (node.type in ('equipamento')) AND (node.status <> 0 OR (node.uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0) OR ***ADMINISTER_NODES*** = 1) AND (workflow_node.sid = 4)
ORDER BY node_sticky DESC, node_data_field_date_field_date_value DESC, node_created_minute DESC, node_title ASC

From the help text on the distinct setting:

This will make the view display only distinct items. If there are multiple identical items, each will be displayed only once. You can use this to try and remove duplicates from a view, though it does not always work.

Please actually read stuff before you post and change the status of issues around. It doesn't always work. It says so. There's nothing I can do about it. If DISTINCT doesn't do the trick then you need a new approach.

#7: With that query, if you have more than one term or file on a node you will get multiple rows for that node, and each will be distinct as far as the db is concerned.

Committed to 3.x line too. Thanks!

I think 6.14 may have broken this. See #284392: db_rewrite_sql causing issues with DISTINCT, comments after #154.

Patch for Views 6.x-2.6.

A cleaner implementation of #13. Patch for Views 6.x-2.6.

A cleaner implementation of #13. Patch for Views 6.x-2.6.

It works!!! very nice work..

The weekend can start!

Works fine for the problen with 6.14 and -2.x.

subscribe

I need a patch that works with both 6.13 and 6.14 -- because I can't guarantee that anybody will be running a particular version.

Actually: Please post the patch in http://drupal.org/node/579892 instead and return this to its original state.

6.14 was a security release, so we can force people to be on that version, yes?

I applied the patch in #15 to the latest 6.x-2.x-dev version and it has solved the problems of duplicates and triplicates in Drupal 6.14

Thanks,

Izzy

Closing this issue since these workarounds are for versions of Drupal before 6.14.

If you are using Drupal 6.14, please see Authors and only authors see dupplicate items after upgrading Drupal to 6.14 and db_rewrite_sql causing issues with DISTINCT.

I do not get the patch that went in (#3): why *in hell* do you prevent distinct for being set if the base table is not node?

subscribe

And where to put this patch ?

DamZ is right. It should prevent distinct from being set if the base table is 'node'.

The original patch here needs to be reverted.

Original patch reverted from 2.x and 3.x versions.

Subscribing.

This is now taken care of in #579892: Authors and only authors see duplicate items after upgrading Drupal to 6.14.